﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-04-08
修改:
修改时间: 
功能: 小品类SKC数
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using com.nm.library.Helper;
using Nancy.Extensions;
using com.nm;
using com.nm.library;

namespace PlanAPI
{
    [Description("小品类SKC数")]
    public class PLItemSkcPcsHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-04-08
        [MustLogin]
        [Description("小品类SKC数列表")]
        [ReturnType(typeof(PaperData<PLItemSkcPcsModel.ListModel>))]
        public ApiResult GetPLItemSkcPcsList([ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")] string KindID = "", [ParamDescription("系列")] string SeriesID = "",
            [ParamDescription("店铺")] string ShopIDs = "", [ParamDescription("企划周")] string Weekv = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var shopList = string.IsNullOrEmpty(ShopIDs)
                    ? ""
                    : string.Join(",", ShopIDs.Split(',').Select(item => "'" + item + "'"));
            var count = ReturnPLItemSkcPcsCount(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, shopList);
            var result = PaperData<PLItemSkcPcsModel.ListModel>.Create(count, Size, Page);
            var list = ReturnPLItemSkcPcsList(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, shopList, Weekv, Page, Size)
                .GroupBy(d => new { d.Yearv, d.Monthv, d.Weekv, d.ParentAreaID, d.ShopID, d.KindID, d.SeriesID })
                        .ToList();
            var query = list.Select(item => new PLItemSkcPcsModel.ListModel()
            {
                Yearv= item.Key.Yearv,
                Monthv= item.Key.Monthv,
                Weekv= item.Key.Weekv,
                ParentAreaID= item.Key.ParentAreaID,
                ParentAreaName= item.First().ParentAreaName,
                ShopID= item.Key.ShopID,
                ShopCode= item.First().ShopCode,
                ShopName= item.First().ShopName,
                KindID= item.Key.KindID,
                KindName= item.First().KindName,
                SeriesID= item.Key.SeriesID,
                SeriesName= item.First().SeriesName,
                list= item.GroupBy(
                            d =>
                                d.Yearv + "_" + d.Monthv + "_" + d.Weekv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                d.KindID + "_" + d.SeriesID + "_" + d.MaterialProperty + "_" + d.ModelID).ToList(),
            }).ToList();
            result.Items = query;
            return Success(result);
        }

        /// <summary>
        /// 返回小品类SKC数列表
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-08
        public IList<PLItemSkcPcsModel.ListModel> ReturnPLItemSkcPcsList(long Yearv, long Monthv, string ParentAreaID, string KindID, string SeriesID, string ShopList, string Weekv, int Page,int Size)
        {
            try
            {
                var sql = $@"SELECT  table2.* ,
        pcs.MaterialProperty ,
        pcs.ModelID ,
        pcs.ItemID ,
        pcs.Weekv ,
        pcs.CheckWeekvPlanSKC ,
        pcs.MatPCkPtStandAmountRate ,
        pcs.ModelCkPtStandAmountRate ,
        pcs.ItemCkPtStandAmountRate ,
        pcs.CKMoveDeepQty ,
        pcs.AvgPrice ,
        mi.ItemName ,
        model.ModelName ,
        pcs.PtStandAmountRate ,
        pcs.SKCQty ,
        ISNULL(pcs.CkSKCQty, pcs.SKCQty) AS CkSKCQty
FROM    ( SELECT    *
          FROM      ( SELECT    pcs.ExtrSource ,
                                pcs.CompanyID ,
                                pcs.Yearv ,
                                pcs.Monthv ,
                                pcs.ParentAreaID ,
                                pcs.ShopID ,
                                pcs.KindID ,
                                pcs.SeriesID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                dict.dictName AS ParentAreaName ,
                                kind.KindName ,
                                series.SeriesName ,
                                ROW_NUMBER() OVER ( ORDER BY pcs.Yearv DESC , pcs.Monthv DESC , pcs.ParentAreaID , pcs.ShopID , pcs.KindID , pcs.SeriesID ) AS Row
                      FROM      ngoods.ngoods_PL_Item_SkcPcs pcs
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON pcs.ExtrSource = shop.ExtrSource
                                                     AND pcs.CompanyID = shop.CompanyID
                                                     AND pcs.ShopID = shop.ShopID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON pcs.ParentAreaID = dict.dictValue
                                                     AND pcs.ExtrSource = dict.ExtrSource
                                                     AND pcs.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON pcs.KindID = kind.KindID
                                                     AND pcs.ExtrSource = kind.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Series
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) series ON pcs.SeriesID = series.SeriesID
                                                       AND pcs.ExtrSource = series.ExtrSource
                      WHERE     pcs.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND pcs.CompanyID = '{UserInfo.UserCompanyId}'
                                AND pcs.Yearv = {Yearv}
                                AND pcs.Monthv = {Monthv}
                                AND pcs.CheckWeekvPlanSKC > 0
                                {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pcs.ParentAreaID='{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(KindID) ? "" : $@" and pcs.KindID='{KindID}' ")}
                                {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pcs.SeriesID='{SeriesID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                      GROUP BY  pcs.ExtrSource ,
                                pcs.CompanyID ,
                                pcs.Yearv ,
                                pcs.Monthv ,
                                pcs.ParentAreaID ,
                                pcs.ShopID ,
                                pcs.KindID ,
                                pcs.SeriesID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                dict.dictName ,
                                kind.KindName ,
                                series.SeriesName
                    ) table1
          WHERE     Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page}
                    * {Size}
        ) table2
        LEFT JOIN ( SELECT  pcs.*
                    FROM    ngoods.ngoods_PL_Item_SkcPcs pcs
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON pcs.ExtrSource = shop.ExtrSource
                                                 AND pcs.CompanyID = shop.CompanyID
                                                 AND pcs.ShopID = shop.ShopID
                    WHERE   pcs.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND pcs.CompanyID = '{UserInfo.UserCompanyId}'
                            AND pcs.Yearv = {Yearv}
                            AND pcs.Monthv = {Monthv}
                            AND pcs.CheckWeekvPlanSKC > 0
                            {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pcs.ParentAreaID='{ParentAreaID}' ")}
                            {(string.IsNullOrEmpty(KindID) ? "" : $@" and pcs.KindID='{KindID}' ")}
                            {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pcs.SeriesID='{SeriesID}' ")}
                            {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                            {(string.IsNullOrEmpty(Weekv) ? "" : $@" and pcs.Weekv={Weekv} ")}
                    
                  ) pcs ON table2.ExtrSource = pcs.ExtrSource
                           AND table2.CompanyID = pcs.CompanyID
                           AND table2.Yearv = pcs.Yearv
                           AND table2.Monthv = pcs.Monthv
                           AND table2.ParentAreaID = pcs.ParentAreaID
                           AND table2.ShopID = pcs.ShopID
                           AND table2.KindID = pcs.KindID
                           AND table2.SeriesID = pcs.SeriesID
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Item
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) mi ON pcs.ItemID = mi.ItemID
                           AND table2.ExtrSource = mi.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Model
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) model ON pcs.ModelID = model.ModelID
                              AND table2.ExtrSource = model.ExtrSource
ORDER BY table2.Yearv DESC ,
        table2.Monthv DESC ,
        table2.ParentAreaID ,
        table2.ShopID ,
        table2.KindID ,
        table2.SeriesID ,
        pcs.MaterialProperty ,
        pcs.ModelID ,
        pcs.Weekv ,
        pcs.ItemID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLItemSkcPcsModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回小品类SKC数总条数
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-08
        public int ReturnPLItemSkcPcsCount(long Yearv, long Monthv, string ParentAreaID, string KindID, string SeriesID, string ShopList)
        {
            try
            {
                var sql = $@" SELECT    COUNT(1)
          FROM      ( SELECT    COUNT(1)
                      FROM      ngoods.ngoods_PL_Item_SkcPcs pcs
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_Shop
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) shop ON pcs.ExtrSource = shop.ExtrSource
                                                     AND pcs.CompanyID = shop.CompanyID
                                                     AND pcs.ShopID = shop.ShopID
                                INNER JOIN ( SELECT *
                                             FROM   ndw.ndw_global_dictionary
                                             WHERE  ExtrSource = 'KM'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                           ) dict ON pcs.ParentAreaID = dict.dictValue
                                                     AND pcs.ExtrSource = dict.ExtrSource
                                                     AND pcs.CompanyID = dict.CompanyID
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Kind
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) kind ON pcs.KindID = kind.KindID
                                                     AND pcs.ExtrSource = kind.ExtrSource
                                INNER JOIN ( SELECT *
                                             FROM   nods.nods_erp_SD_Mat_Series
                                             WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                           ) series ON pcs.SeriesID = series.SeriesID
                                                       AND pcs.ExtrSource = series.ExtrSource
                      WHERE     pcs.ExtrSource = '{UserInfo.UserExtrSource}'
                                AND pcs.CompanyID = '{UserInfo.UserCompanyId}'
                                AND pcs.Yearv = {Yearv}
                                AND pcs.Monthv = {Monthv}
                                AND pcs.CheckWeekvPlanSKC > 0
                                {(string.IsNullOrEmpty(ParentAreaID) ? "" : $@" and pcs.ParentAreaID='{ParentAreaID}' ")}
                                {(string.IsNullOrEmpty(KindID) ? "" : $@" and pcs.KindID='{KindID}' ")}
                                {(string.IsNullOrEmpty(SeriesID) ? "" : $@" and pcs.SeriesID='{SeriesID}' ")}
                                {(string.IsNullOrEmpty(ShopList) ? "" : $@" and shop.ShopID in ({ShopList}) ")}
                      GROUP BY  pcs.ExtrSource ,
                                pcs.ExtrSource ,
                                pcs.Yearv ,
                                pcs.Monthv ,
                                pcs.ParentAreaID ,
                                pcs.ShopID ,
                                pcs.KindID ,
                                pcs.SeriesID ,
                                shop.ShopCode ,
                                shop.ShopName ,
                                dict.dictName ,
                                kind.KindName ,
                                series.SeriesName
                    ) table1";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        ///// Writer：梁家健
        ///// Create Date：2017-04-08
        //[MustLogin]
        //[Description("生成小品类SKC数列表")]
        //[ReturnType(typeof(int),"0-成功")]
        //public ApiResult CreatPLItemSkcPcs([ParamDescription("企划年")] long Yearv, [ParamDescription("企划月")] long Monthv)
        //{
        //    try
        //    {
        //        var sqlDelSkcPcs = $@"delete from goods.goods_PL_Item_SkcPcs where Yearv={Yearv} and Monthv={Monthv}";
        //        var sqlDelSkcOperLog =
        //            $@"delete from goods.goods_PL_Item_SkcOperLog where Yearv={Yearv} and Monthv={Monthv}";
        //        var list = ReturnPLItemSkcPcsList(Yearv, Monthv);
        //        var pcsCmdList = new List<CommandInfo>()
        //        {
        //            new CommandInfo()
        //            {
        //                CommandText = sqlDelSkcPcs,
        //            },
        //        };
        //        var logCmdList = new List<CommandInfo>()
        //        {
        //            new CommandInfo()
        //            {
        //                CommandText = sqlDelSkcOperLog,
        //            },
        //        };
        //        var query = list.Select(d => new PLItemSkcPcsModel.ListModel()
        //        {
        //            Yearv = d.Yearv,
        //            Monthv = d.Monthv,
        //            Weekv = d.Weekv,
        //            ParentAreaID = d.ParentAreaID,
        //            ShopID = d.ShopID,
        //            KindID = d.KindID,
        //            SeriesID = d.SeriesID,
        //            MaterialProperty = d.MaterialProperty,
        //            ModelID = d.ModelID,
        //            ItemID = d.ItemID,
        //            StandAmountRate =
        //                (d.CKMoveDeepQty * (decimal?)d.AvgPrice) == 0 ? 0 : d.MatPCkPtStandAmountRate * d.ModelCkPtStandAmountRate *
        //                                (d.ItemCkPtStandAmountRate / (d.CKMoveDeepQty * (decimal?)d.AvgPrice)),
        //        });
        //        var pcsList = new List<PLItemSkcPcsModel.CreatModel>();
        //        foreach (var item in query)
        //        {
        //            var sumStandAmountRate = query.Where(r => r.Yearv == item.Yearv && r.Monthv == item.Monthv && r.Weekv == item.Weekv && r.ParentAreaID == item.ParentAreaID && r.ShopID == item.ShopID && r.KindID == item.KindID && r.SeriesID == item.SeriesID).Sum(r => r.StandAmountRate);
        //            var ptStandAmountRate = sumStandAmountRate == 0 ? 0 : item.StandAmountRate / sumStandAmountRate;
        //            var pcs = new PLItemSkcPcsModel.CreatModel()
        //            {
        //                CompanyID = "KM",
        //                Yearv = item.Yearv,
        //                Monthv = item.Monthv,
        //                Weekv = item.Weekv,
        //                ParentAreaID = item.ParentAreaID,
        //                ShopID = item.ShopID,
        //                KindID = item.KindID,
        //                SeriesID = item.SeriesID,
        //                MaterialProperty = item.MaterialProperty,
        //                ModelID = item.ModelID,
        //                ItemID = item.ItemID,
        //                StandAmountRate = item.StandAmountRate,
        //                PtStandAmountRate = ptStandAmountRate,
        //                SKCQty = Convert.ToInt64(Math.Round(Convert.ToDouble(item.CheckWeekvPlanSKC * ptStandAmountRate), 0, MidpointRounding.AwayFromZero)),
        //            };
        //            pcsList.Add(pcs);
        //        }
        //        var pcsDt = DataTableHelper.LinqToDataTable(pcsList.ToList().DistinctBy(d=>new
        //        {
        //            d.Yearv,
        //            d.Monthv,
        //            d.Weekv,
        //            d.ParentAreaID,
        //            d.ShopID,
        //            d.KindID,
        //            d.SeriesID,
        //            d.MaterialProperty,
        //            d.ModelID,
        //            d.ItemID,
        //        }));
        //        var logList =
        //            pcsList.GroupBy(
        //                d =>
        //                    new
        //                    {
        //                        d.Yearv,
        //                        d.Monthv,
        //                        d.Weekv,
        //                        d.KindID,
        //                        d.SeriesID,
        //                        d.MaterialProperty,
        //                        d.ModelID,
        //                        d.ItemID
        //                    }).Select(d => new PLItemSkcOperLogModel.CreatModel()
        //            {
        //                CompanyID = d.First().CompanyID,
        //                Yearv = d.Key.Yearv,
        //                Monthv = d.Key.Monthv,
        //                Weekv = d.Key.Weekv,
        //                KindID = d.Key.KindID,
        //                SeriesID = d.Key.SeriesID,
        //                MaterialProperty = d.Key.MaterialProperty,
        //                ModelID = d.Key.ModelID,
        //                ItemID = d.Key.ItemID,
        //            });
        //        var logDt = DataTableHelper.LinqToDataTable(logList.ToList().DistinctBy(d => new
        //        {
        //            d.Yearv,
        //            d.Monthv,
        //            d.Weekv,
        //            d.KindID,
        //            d.SeriesID,
        //            d.MaterialProperty,
        //            d.ModelID,
        //            d.ItemID,
        //        }));
        //        Helper.ExecuteSqlTran(pcsCmdList, pcsDt, "goods.goods_PL_Item_SkcPcs");
        //        Helper.ExecuteSqlTran(logCmdList, logDt, "goods.goods_PL_Item_SkcOperLog");
        //        return Success(0);
        //    }
        //    catch (Exception ex)
        //    {
        //        return Error(ex.Message);
        //    }
        //}

        /// Writer：梁家健
        /// Create Date：2017-04-17
        [MustLogin]
        [Description("生成小品类SKC数列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CreatPLItemSkcPcs([ParamDescription("企划年")] long Yearv=-1L, [ParamDescription("企划月")] long Monthv = -1L)
        {
            var uid = Guid.NewGuid().ToString().Replace("-", "");
            var tempTableName = $" ngoods.temp{uid} ";
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (Yearv == -1L || Monthv == -1L)
                {
                    var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                    if (set == null) return Error("没有要修改企划的月份");
                    Yearv = set.Yearv.Value;
                    Monthv = set.Monthv.Value;
                }
                DropTempTable(tempTableName);
                CreatCalcItemSkc(Yearv, Monthv, tempTableName);
                UpdateCalcItemSkc(tempTableName);
                UpdateCalcItemSkcSKCQty(tempTableName);
                MergeSkcPcs(Yearv, Monthv, tempTableName);
                CreatSkcOperLog(Yearv, Monthv);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
            finally
            {
                DropTempTable(tempTableName);
            }
        }


//        public void InsertCalcItemSkc(long Yearv, long Monthv)
//        {
//            try
//            {
//                var sqlDel = $@"delete from goods.goods_PL_CalcItemSkc";
//                var sql = $@"
//insert into goods.goods_PL_CalcItemSkc(CompanyID ,
//        Yearv ,
//        Monthv ,
//        Weekv ,
//        ParentAreaID ,
//        ShopID ,
//        KindID ,
//        SeriesID ,
//        MaterialProperty ,
//        ModelID ,
//        ItemID ,
//        CKMoveDeepQty ,
//        AvgPrice ,
//        ItemCkPtStandAmountRate ,
//        ModelCkPtStandAmountRate ,
//        MatPCkPtStandAmountRate ,
//        CheckWeekvPlanSKC ,StandAmountRate,PtStandAmountRate,SKCQty)
//        select 
//CompanyID ,
//        Yearv ,
//        Monthv ,
//        Weekv ,
//        ParentAreaID ,
//        ShopID ,
//        KindID ,
//        SeriesID ,
//        MaterialProperty ,
//        ModelID ,
//        ItemID ,
//        CKMoveDeepQty ,
//        AvgPrice ,
//        ItemCkPtStandAmountRate ,
//        ModelCkPtStandAmountRate ,
//        MatPCkPtStandAmountRate ,
//        CheckWeekvPlanSKC ,
//        CASE WHEN ISNULL(AvgPrice, 0) = 0
//                  OR ISNULL(CKMoveDeepQty, 0) = 0 THEN 0
//             ELSE ItemCkPtStandAmountRate * ModelCkPtStandAmountRate
//                  * MatPCkPtStandAmountRate / ( AvgPrice * CKMoveDeepQty )
//        END AS StandAmountRate ,
//        0.00 AS PtStandAmountRate ,
//        0 AS SKCQty   
//FROM    goods.vw_show_ItemSkc
//WHERE   CompanyID = '{UserInfo.UserCompanyId}'
//        AND Yearv = {Yearv}
//        AND Yearv = {Yearv}
//        AND Monthv = {Monthv}";
//                var cmdList = new List<CommandInfo>() {
//                    new CommandInfo() {
//                        CommandText = sqlDel
//                    },
//                    new CommandInfo() {
//                        CommandText = sql
//                    }
//                };
//                Helper.ExecuteSqlTran(cmdList);
//            }
//            catch (Exception ex)
//            {
//                throw (ex);
//            }
//        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch(Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void CreatCalcItemSkc(long Yearv,long Monthv,string TempTableName)
        {
            try
            {
                var sql = $@"
SELECT  CompanyID ,
        ExtrSource ,
        Yearv ,
        Monthv ,
        Weekv ,
        ParentAreaID ,
        ShopID ,
        KindID ,
        SeriesID ,
        MaterialProperty ,
        ModelID ,
        ItemID ,
        CKMoveDeepQty ,
        AvgPrice ,
        ItemCkPtStandAmountRate ,
        ModelCkPtStandAmountRate ,
        MatPCkPtStandAmountRate ,
        CheckWeekvPlanSKC ,
        CASE WHEN ISNULL(AvgPrice, 0) = 0
                  OR ISNULL(CKMoveDeepQty, 0) = 0 THEN 0
             ELSE ItemCkPtStandAmountRate * ModelCkPtStandAmountRate
                  * MatPCkPtStandAmountRate / ( AvgPrice * CKMoveDeepQty )
        END AS StandAmountRate ,
        CAST(0.0 AS DECIMAL(20, 4)) AS PtStandAmountRate ,
        0 AS SKCQty
INTO    {TempTableName}
FROM    ngoods.nvw_show_ItemSkc
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND Yearv = {Yearv}
        AND Monthv = {Monthv}";
                var cmdList = new List<CommandInfo>() {
                    new CommandInfo() {
                        CommandText = sql
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 更新临时表字段PtStandAmountRate
        /// </summary>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void UpdateCalcItemSkc(string TempTableName)
        {
            try
            {
                var sql = $@"update {TempTableName} a set
PtStandAmountRate = cast(
						case when m.StandAmountRate=0 then 0 else a.StandAmountRate/m.StandAmountRate end 
						as decimal(20,4)
						)
from(
    SELECT  CompanyID ,
            ExtrSource ,
            Yearv ,
            Monthv ,
            Weekv ,
            ParentAreaID ,
            ShopID ,
            KindID ,
            SeriesID ,
            SUM(StandAmountRate) AS StandAmountRate
    FROM    {TempTableName}
    WHERE CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
    GROUP BY CompanyID ,
            ExtrSource ,
            Yearv ,
            Monthv ,
            Weekv ,
            ParentAreaID ,
            ShopID ,
            KindID ,
            SeriesID
)m where m.ExtrSource = a.ExtrSource and m.CompanyID = a.CompanyID and m.Yearv = a.Yearv and m.Monthv = a.Monthv and m.Weekv = a.Weekv
and m.ShopID =a.ShopID  and m.KindID = a.KindID and m.SeriesID = a.SeriesID";
                var cmdList = new List<CommandInfo>() {
                    new CommandInfo() {
                        CommandText = sql
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 更新临时表字段SKCQty
        /// </summary>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void UpdateCalcItemSkcSKCQty(string TempTableName)
        {
            try
            {
                var sql = $@"UPDATE  {TempTableName}
SET     SKCQty = CheckWeekvPlanSKC * PtStandAmountRate
WHERE   CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                var cmdList = new List<CommandInfo>() {
                    new CommandInfo() {
                        CommandText = sql
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 更新SKC数量
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void MergeSkcPcs(long Yearv, long Monthv,string TempTableName)
        {
            try
            {
                const string targetTableName = @" ngoods.ngoods_PL_Item_SkcPcs ";
                const string onConditions = @" targetTable.CompanyID = sourceTable.CompanyID
                         AND targetTable.ExtrSource = sourceTable.ExtrSource
                         AND targetTable.Yearv = sourceTable.Yearv
                         AND targetTable.Monthv = sourceTable.Monthv
                         AND targetTable.Weekv = sourceTable.Weekv
                         AND targetTable.ShopID = sourceTable.ShopID
                         AND targetTable.KindID = sourceTable.KindID
                         AND targetTable.SeriesID = sourceTable.SeriesID
                         AND targetTable.MaterialProperty = sourceTable.MaterialProperty
                         AND targetTable.ModelID = sourceTable.ModelID
                         AND targetTable.ItemID = sourceTable.ItemID ";
                const string setConditions = @" StandAmountRate = sourceTable.StandAmountRate ,
                    PtStandAmountRate = sourceTable.PtStandAmountRate ,
                    CheckWeekvPlanSKC = sourceTable.CheckWeekvPlanSKC ,
                    MatPCkPtStandAmountRate = sourceTable.MatPCkPtStandAmountRate ,
                    ModelCkPtStandAmountRate = sourceTable.ModelCkPtStandAmountRate ,
                    ItemCkPtStandAmountRate = sourceTable.ItemCkPtStandAmountRate ,
                    AvgPrice = sourceTable.AvgPrice ,
                    CKMoveDeepQty = sourceTable.CKMoveDeepQty ,
                    SKCQty = sourceTable.SKCQty ,
                    CkSKCQty = ( CASE targetTable.CheckState
                                   WHEN 1 THEN targetTable.CkSKCQty
                                   ELSE sourceTable.SKCQty
                                 END ) ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      Yearv ,
                      Monthv ,
                      Weekv ,
                      ParentAreaID ,
                      ShopID ,
                      KindID ,
                      SeriesID ,
                      MaterialProperty ,
                      ModelID ,
                      ItemID ,
                      StandAmountRate ,
                      PtStandAmountRate ,
                      SKCQty ,
                      CkSKCQty ,
                      CheckWeekvPlanSKC ,
                      MatPCkPtStandAmountRate ,
                      ModelCkPtStandAmountRate ,
                      ItemCkPtStandAmountRate ,
                      AvgPrice ,
                      CKMoveDeepQty ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.Yearv ,
                      sourceTable.Monthv ,
                      sourceTable.Weekv ,
                      sourceTable.ParentAreaID ,
                      sourceTable.ShopID ,
                      sourceTable.KindID ,
                      sourceTable.SeriesID ,
                      sourceTable.MaterialProperty ,
                      sourceTable.ModelID ,
                      sourceTable.ItemID ,
                      sourceTable.StandAmountRate ,
                      sourceTable.PtStandAmountRate ,
                      sourceTable.SKCQty ,
                      sourceTable.SKCQty ,
                      sourceTable.CheckWeekvPlanSKC ,
                      sourceTable.MatPCkPtStandAmountRate ,
                      sourceTable.ModelCkPtStandAmountRate ,
                      sourceTable.ItemCkPtStandAmountRate ,
                      sourceTable.AvgPrice ,
                      sourceTable.CKMoveDeepQty ";
                Helper.MergeIntoTable(TempTableName, targetTableName, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 更新日志表数据
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// Writer：梁家健
        /// Create Date：2017-04-17
        public void CreatSkcOperLog(long Yearv, long Monthv)
        {
            try
            {
                var sqlDel = $@"delete from ngoods.ngoods_PL_Item_SkcOperLog where CompanyID = '{UserInfo.UserCompanyId}' and CompanyID = '{UserInfo.UserCompanyId}' and Yearv={Yearv} and Monthv={Monthv}";
                var sql = $@"INSERT  INTO ngoods.ngoods_PL_Item_SkcOperLog
        ( CompanyID ,
          ExtrSource ,
          Yearv ,
          Monthv ,
          Weekv ,
          KindID ,
          SeriesID ,
          MaterialProperty ,
          ModelID ,
          ItemID
        )
        SELECT  CompanyID ,
                ExtrSource ,
                Yearv ,
                Monthv ,
                Weekv ,
                KindID ,
                SeriesID ,
                MaterialProperty ,
                ModelID ,
                ItemID
        FROM    ngoods.ngoods_PL_Item_SkcPcs
        WHERE   CompanyID = '{UserInfo.UserCompanyId}'
                AND ExtrSource = '{UserInfo.UserExtrSource}'
                AND Yearv = {Yearv}
                AND Monthv = {Monthv}
        GROUP BY CompanyID ,
                ExtrSource ,
                Yearv ,
                Monthv ,
                Weekv ,
                KindID ,
                SeriesID ,
                MaterialProperty ,
                ModelID ,
                ItemID";
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText = sqlDel
                    },
                    new CommandInfo()
                    {
                        CommandText = sql
                    },
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch(Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 返回小品类SKC数列表
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-13
        public IList<PLItemSkcPcsModel.ListModel> ReturnPLItemSkcPcsList(long Yearv, long Monthv)
        {
            try
            {
                var sql = $@"SELECT  item.* 
FROM    ngoods.nvw_show_ItemSkc item
WHERE   item.ExtrSource = '{UserInfo.UserExtrSource}'
        AND item.CompanyID = '{UserInfo.UserCompanyId}'
        AND item.Yearv = {Yearv}
        AND item.Monthv = {Monthv}
ORDER BY item.Yearv DESC ,
        item.Monthv DESC ,
        item.ParentAreaID ,
        item.ShopID ,
        item.KindID ,
        item.SeriesID ,
        item.MaterialProperty ,
        item.ModelID ,
        item.Weekv ,
        item.ItemID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLItemSkcPcsModel.ListModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-08
        [JsonType]
        [MustLogin]
        [Description("生成小品类SKC数列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdatePLItemSkcPcs([ParamDescription("小品类SKC数")] PLItemSkcPcsModel.ModifyModel param)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var sql = $@" UPDATE  ngoods.ngoods_PL_Item_SkcPcs
SET     CkSKCQty = {{0}} ,
        CheckState = 1
WHERE   Yearv = {{1}}
        AND Monthv = {{2}}
        AND Weekv = {{3}}
        AND ParentAreaID = '{{4}}'
        AND ShopID = '{{5}}'
        AND KindID = '{{6}}'
        AND SeriesID = '{{7}}'
        AND MaterialProperty = '{{8}}'
        AND ModelID = '{{9}}'
        AND ItemID = '{{10}}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'; ";
                if (param == null) return Error("参数对象为空");
                var sb = new StringBuilder();
                if (param.list != null)
                {
                    foreach (var item in param.list)
                    {
                            sb.AppendLine(
                                string.Format(sql, item.CkSKCQty, item.Yearv, item.Monthv, item.Weekv, item.ParentAreaID,
                                    item.ShopID, item.KindID, item.SeriesID, item.MaterialProperty, item.ModelID,
                                    item.ItemID));
                    }
                    var cmd = Helper.GetSqlStringCommand(sb.ToString());
                    Helper.ExecuteNonQuery(cmd);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 根据小品类返回小品类SKC数列表
        /// </summary>
        /// <param name="ItemID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-12
        public IList<PLItemSkcPcsModel.NeceModel> ReturnAllPLItemSkcPcsList(long Yearv, long Monthv, long Weekv, string KindID, string SeriesID, string MaterialProperty, string ItemIDList)
        {
            try
            {
                var sql =
                    $@"SELECT  *
FROM    ngoods.ngoods_PL_Item_SkcPcs
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND Yearv = {Yearv}
        AND Monthv = {Monthv}
        AND Weekv = {Weekv} 
        {(string.IsNullOrEmpty(KindID) ? "" : $" and KindID='{KindID}' ")} 
        {(string.IsNullOrEmpty(SeriesID) ? "" : $" and SeriesID='{SeriesID}' ")} 
        {(string.IsNullOrEmpty(MaterialProperty) ? "" : $" and MaterialProperty='{MaterialProperty}' ")} 
        {(string.IsNullOrEmpty(ItemIDList) ? "" : $" and ItemID in ({ItemIDList}) ")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLItemSkcPcsModel.NeceModel>.ConvertToModel(dt);
            }
            catch(Exception ex)
            {
                throw (ex);
            }
        }
    }
}
